package com.habdqn.easybuy.dao.impl;

import com.habdqn.easybuy.dao.BaseDao;
import com.habdqn.easybuy.dao.UserDao;
import com.habdqn.easybuy.entity.User;
import com.habdqn.easybuy.util.Page;
import com.habdqn.easybuy.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Xing.Jiejian
 * @date 2020/11/26
 */
public class UserDaoImpl extends BaseDao implements UserDao {
    private final String baseSelect = "SELECT id, username, password, sex, birthday, identity_code, email, mobile," +
            " address, status FROM user";

    @Override
    public User queryById(Integer id) {
        String sql = baseSelect + " WHERE id=?";
        List<User> users = getListByRS(executeQuery(sql, id));
        if (users != null && users.size() > 0) {
            return users.get(0);
        }
        return null;
    }

    /**
     * 通过用户名查询用户
     *
     * @param username 用户名
     * @return 实例对象
     */
    @Override
    public User queryByUsername(String username) {
        String sql = baseSelect + " WHERE username=?";
        List<User> users = getListByRS(executeQuery(sql, username));
        if (users != null && users.size() > 0) {
            return users.get(0);
        }
        return null;
    }

    @Override
    public List<User> queryAllByLimit(int offset, int limit) {
        String sql = baseSelect + " LIMIT ?,?";
        return getListByRS(executeQuery(sql, offset, limit));
    }

    @Override
    public List<User> queryAll(User user, Page<User> page) {
        StringBuilder sql = new StringBuilder(baseSelect);
        List<Object> paramList = buildQueryConditions(user, sql);
        buildPageQuery(sql,paramList,page);
        return getListByRS(executeQuery(sql.toString(), paramList.toArray()));
    }

    @Override
    public int insert(User user) {
        String sql = "INSERT INTO user(username, password, sex, birthday, identity_code, email, mobile, address," +
                " status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {user.getUsername(), user.getPassword(), user.getSex(), user.getBirthday(),
                user.getIdentityCode(), user.getEmail(), user.getMobile(), user.getAddress(), user.getStatus()};
        return executeInsert(sql, params);
    }

    @Override
    public int update(User user) {
        String sql = "UPDATE user SET username=?, password=?, sex=?, birthday=?, identity_code=?, email=?, mobile=?," +
                " address=?, status=? WHERE id=?";
        Object[] params = {user.getUsername(), user.getPassword(), user.getSex(), user.getBirthday(),
                user.getIdentityCode(), user.getEmail(), user.getMobile(), user.getAddress(), user.getStatus(),
                user.getId()};
        return executeUpdate(sql, params);
    }

    @Override
    public int deleteById(Integer id) {
        String sql = "DELETE FROM user WHERE id=?";
        return executeUpdate(sql, id);
    }

    /**
     * 获取总数
     *
     * @param user 查询条件
     * @return 总记录数
     */
    @Override
    public int getTotal(User user) {
        StringBuilder sql = new StringBuilder(" SELECT COUNT(id) FROM user ");
        List<Object> paramList = buildQueryConditions(user, sql);
        return (int) (long) uniqueResult(sql.toString(), paramList.toArray());
    }

    /**
     * 组装关于User的查询条件
     * @param user 查询条件构成的User对象
     * @param sql 原始查询SQL
     * @return 参数集合
     */
    private List<Object> buildQueryConditions(User user, StringBuilder sql) {
        List<Object> paramList = new ArrayList<>();
        if (user != null) {
            sql.append(" WHERE 1=1 ");
            if (user.getId()!=null && user.getId() > 0) {
                sql.append(" AND id=? ");
                paramList.add(user.getId());
            }
            // 用户名模糊查询
            if (StringUtils.isNotBlank(user.getUsername())) {
                sql.append(" AND username LIKE ? ");
                paramList.add("%" + user.getUsername() + "%");
            }
            // 密码不查询
            // 性别
            if (StringUtils.isNotBlank(user.getSex())) {
                sql.append(" AND sex=? ");
                paramList.add(user.getSex());
            }
            // 生日
            if (user.getBirthday() != null) {
                sql.append(" AND birthday=? ");
                paramList.add(user.getBirthday());
            }
            // 身份证号
            if (StringUtils.isNotBlank(user.getIdentityCode())) {
                sql.append(" AND identity_code=? ");
                paramList.add(user.getIdentityCode());
            }
            // 邮箱
            if (StringUtils.isNotBlank(user.getEmail())) {
                sql.append(" AND email=? ");
                paramList.add(user.getEmail());
            }
            // 手机号
            if (StringUtils.isNotBlank(user.getMobile())) {
                sql.append(" AND mobile=? ");
                paramList.add(user.getMobile());
            }
            // 地址：模糊查询
            if (StringUtils.isNotBlank(user.getAddress())) {
                sql.append(" AND address LIKE ? ");
                paramList.add("%" + user.getAddress() + "%");
            }
            // 状态
            if (user.getStatus()!=null && user.getStatus() >= 0) {
                sql.append(" AND status=? ");
                paramList.add(user.getStatus());
            }
        }
        return paramList;
    }

    /**
     * 从结果集中提取对象集合
     *
     * @param rs 结果集
     * @return 用户集合
     */
    private List<User> getListByRS(ResultSet rs) {
        List<User> users = null;
        try {
            if (rs != null) {
                users = new ArrayList<>();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setSex(rs.getString("sex"));
                    user.setBirthday(rs.getDate("birthday"));
                    user.setIdentityCode(rs.getString("identity_code"));
                    user.setEmail(rs.getString("email"));
                    user.setMobile(rs.getString("mobile"));
                    user.setAddress(rs.getString("address"));
                    user.setStatus(rs.getInt("status"));
                    users.add(user);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return users;
    }
}
